package com.crawler.tables

import java.sql.Statement
import java.util.Date

import com.crawler.db.Datasource
import com.crawler.message.TaskParamMessage
import com.crawler.utils.MD5Utils

import scala.language.postfixOps


object Example{

  def main(args: Array[String]) {
//    kimssInsert("D://kimissUrl.txt")
    update()
  }

  def update(): Unit ={
    //update task set status=0 where type='detail' and status=2
    val connectionPool = Datasource.getConnectionPool("Kimiss").getConnection
    connectionPool.setAutoCommit(false)
    var stmt: Statement = null
    try {
      stmt = connectionPool.createStatement()
      val sql = "update task set status=0 where type='detail' and status=2"
      stmt.execute(sql)
      connectionPool.setAutoCommit(true)
      stmt.close()
      connectionPool.close()
    }catch {
      case e:Exception => e.printStackTrace()
    }
  }

  def select(): Unit ={
    val md5List = List[String]("5aad49d984cb8d4470412c405266897d","98ca985f9c9e2bcb760ac456d33dddb6")
    val connectionPool = Datasource.getConnectionPool("TaoBao").getConnection
    var stmt: Statement = null
    try {
      stmt = connectionPool.createStatement()
      val select =s"select * from task where md5 in ('${md5List.mkString("','")}}')"
      val rs = stmt.executeQuery(select)
      while (rs.next()){
        println(TaskParamMessage(rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),
          rs.getString(7),rs.getInt(8),rs.getInt(9)))
      }
    } catch {
      case e:Exception => e.printStackTrace()
    } finally {
      stmt.close()
      connectionPool.close()
    }
  }

  def bulkTask(): Unit ={
    val connectionPool = Datasource.getConnectionPool("TMall").getConnection
    var stmt: Statement = null
    try {
      stmt = connectionPool.createStatement()
      val sql = "update task set status = case md5 when 'ac5d8a03a11d1f2cb25ccdfa892b963d' then 5 when 'ac5d8a03a11d1f2cb25ccdfa892b963d' then 4  end ,date = '23233' where md5 in ('ac5d8a03a11d1f2cb25ccdfa892b963d','ac5d8a03a11d1f2cb25ccdfa892b963d')"
      stmt.execute(sql)
      stmt.close()
      connectionPool.close()
    }catch {
      case e:Exception => e.printStackTrace()
    }
  }

  def selectTask(): Unit ={
    val taskList = new collection.mutable.ListBuffer[Object]()
    val connectionPool = Datasource.getConnectionPool("TMall").getConnection
    var stmt: Statement = null
    try {
      stmt = connectionPool.createStatement()
      val select = "select * from task where md5 in ('ac5d8a03a11d1f2cb25ccdfa892b963d','ac5d8a03a11d1f2cb25ccdfa892b963d')"
      val st = stmt.executeQuery(select)
      while (st.next()){
        taskList.append(st)
      }
    } catch {
      case e:Exception => e.printStackTrace()
    } finally {
      stmt.close()
      connectionPool.close()
    }
    taskList.map(task => println(task))
  }

  def insert(url:String, id:String): Unit ={
    val connectionPool = Datasource.getConnectionPool("Kimiss").getConnection
    connectionPool.setAutoCommit(false)
    var stmt: Statement = null
    try {
      stmt = connectionPool.createStatement()
      val insert =s"INSERT INTO task(method,host,data,param,header, source,type,status,prior,date,md5) VALUES ('get', '${url}','','','${id}', 'Kimiss','detail',0,20000, 1487769940,'')"
      stmt.execute(insert)
      connectionPool.setAutoCommit(true)
    } catch {
      case e:Exception => e.printStackTrace()
    } finally {
      stmt.close()
      connectionPool.close()
    }
  }

  def delete(): Unit ={

    val connectionPool = Datasource.getConnectionPool("TaoBao").getConnection
    var stmt: Statement = null
    try {
      stmt = connectionPool.createStatement()
      val sql = "delete from task where host = 'https://list.tmall.com/search_product.htm?spm=a221u.7741822.4522092394.96.42qi6j&cat=50026502&sort=s&acm=lb-zebra-139787-486571.1003.8.571037&style=g&q=%B1%A3%CA%AA&from=sn_1_cat-qp&scm=1003.8.lb-zebra-139787-486571.ITEM_14479005463722_571037#J_crumbs'"
      stmt.execute(sql)
    } catch {
      case e:Exception => e.printStackTrace()
    } finally {
      stmt.close()
      connectionPool.close()
    }
  }

  def bulkInsert(): Unit ={
    val beginTime = new Date().getTime
    var tasks = List[TaskParamMessage]()
    for (i <- 0 to 150){
      tasks = TaskParamMessage("get","dd","aa","dssd","ee","fsa","sf",3,0) :: tasks
    }
    val connectionPool = Datasource.getConnectionPool("TMall").getConnection
    connectionPool.setAutoCommit(false);
    val stmt = connectionPool.createStatement();
    try {
      tasks.map(crawlerTask => {
        stmt.addBatch(s"INSERT INTO task VALUES ('${crawlerTask.method}','${crawlerTask.data}','${crawlerTask.host}'" +
          s",'${crawlerTask.param}','${crawlerTask.header}','${crawlerTask.source}','${crawlerTask.method}'" +
          s",${crawlerTask.status},${crawlerTask.prior},'${new Date().getTime}','${MD5Utils.getMd5String(crawlerTask)}')");
      })
      stmt.executeBatch()
      connectionPool.setAutoCommit(true)
      print(new Date().getTime - beginTime)
    } catch {
      case e:Exception => e.printStackTrace()
    } finally {
      stmt.close()
      connectionPool.close()
    }
  }
//
//  def kimssInsert(path :String): Unit ={
//    val connectionPool = Datasource.getConnectionPool("Kimiss").getConnection
//    connectionPool.setAutoCommit(false);
//    val stmt = connectionPool.createStatement();
//    val content = Source.fromFile(new File(path)).getLines()
//    for (item <- content){
//      val id = JSONObject.fromObject(item).getString("id")
//      val url = JSONObject.fromObject(item).getString("url")
//      val mdEn = url + ""+""
//      val insertSql =s"INSERT INTO task(method,host,data,param,header, source,type,status,prior,date,md5) VALUES ('get', '${url}','','','${id}', 'Kimiss','detail',0,20000, 1487769940,'${MD5Utils.getMd5String(mdEn)}')"
//      stmt.addBatch(insertSql)
////      insert(url, id)
//    }
//    stmt.executeBatch();
//    connectionPool.setAutoCommit(true)
//    connectionPool.close()
//  }
}